package com.tianqu.tms.core.webservice.weichat.api.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.core.util.StringUtil;
import com.tianqu.tms.dao.TmsDao;
import com.tianqu.tms.dao.TmsTableNames;
import com.tianqu.tms.dto.MarketCarInfoDto;
import com.tianqu.tms.dto.MarketCarInfoTempDto;
import com.tianqu.tms.dto.MarketCarServiceDto;
import com.tianqu.tms.dto.TmsFragileStickerHistoryDto;
import com.tianqu.tms.dto.TroubleCarInfoDto;

/**
 * Created by wu-xz on 2017/2/10.
 */
public class CollectCarInfoImpl extends TmsDao implements CollectCarInfoIDao  {
    /**
     * @throws TmsDaoException
     */
    public CollectCarInfoImpl() throws TmsDaoException {
    	super();
    }

    @Override
    public List<MarketCarInfoDto> findMarketCarInfoListByInsertTime(MarketCarInfoDto entity) throws TmsDaoException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<MarketCarInfoDto> list = new ArrayList<MarketCarInfoDto>();
        StringBuffer sql = new StringBuffer("SELECT SERIAL,REGISTERSTATUS FROM ");
        sql.append(TmsTableNames.TMS_MARKET_CAR_INFO);
        if (entity.getVin().length() == 6) {
        	sql.append(" WHERE RIGHT(VIN,6) = ? ");
        } else {
        	sql.append(" WHERE RIGHT(VIN,8) = ? ");	
        }
        sql.append(" AND  REGISTERSTATUS NOT IN (3,4) ");
        sql.append(" AND TO_DAYS(INSERTTIME) = TO_DAYS(?)");

        try {
            ps = conn.prepareStatement(sql.toString());
            ps.setString(1,entity.getVin());
            ps.setTimestamp(2,entity.getInsertTime());
            rs = ps.executeQuery();
            while (rs.next()){
                MarketCarInfoDto obj = new MarketCarInfoDto();
                obj.setSerial(rs.getString("SERIAL"));
                obj.setRegisterStatus(rs.getInt("registerStatus"));
                list.add(obj);
            }
        } catch (SQLException e) {
            throw new TmsDaoException(e.toString());
        } finally {
            close(rs, ps);
        }
        return list;
    }
    
    @Override
    public List<MarketCarInfoDto> findMarketCarInfoList(MarketCarInfoDto entity) throws TmsDaoException {
    	PreparedStatement ps = null;
        ResultSet rs = null;
        List<MarketCarInfoDto> list = new ArrayList<MarketCarInfoDto>();
        StringBuffer sql = new StringBuffer("SELECT SERIAL,REGISTERSTATUS FROM ");
        sql.append(TmsTableNames.TMS_MARKET_CAR_INFO);
        if (entity.getVin().length() == 6) {
        	sql.append(" WHERE RIGHT(VIN,6) = ? ");
        } else {
        	sql.append(" WHERE RIGHT(VIN,8) = ? ");	
        }
        sql.append(" AND REGISTERSTATUS NOT IN (3,4) ");
        try {
            ps = conn.prepareStatement(sql.toString());
            ps.setString(1,entity.getVin());
            rs = ps.executeQuery();
            while (rs.next()){
                MarketCarInfoDto obj = new MarketCarInfoDto();
                obj.setSerial(rs.getString("SERIAL"));
                obj.setRegisterStatus(rs.getInt("REGISTERSTATUS"));
                list.add(obj);
            }
        } catch (SQLException e) {
            throw new TmsDaoException(e.toString());
        } finally {
            close(rs, ps);
        }
        return list;
    }
    
    @Override
    public void updateMarketCarInfo(MarketCarInfoDto entity) throws TmsDaoException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" UPDATE  ");
        sql.append(TmsTableNames.TMS_MARKET_CAR_INFO);
        sql.append(" SET PHOTO_1 = ?, ");
        sql.append("PHOTO_2 = ?, ");
        sql.append("PHOTO_3 = ?, ");
        sql.append("PHOTO_4 = ?, ");
        sql.append("PHOTO_5 = ?, ");
        sql.append("PHOTO_6 = ?, ");
        sql.append("PHOTO_7 = ?, ");
        sql.append("PHOTO_8 = ?, ");
        sql.append("COLOR = ?, ");
        sql.append("MILEAGE = ?, ");
        sql.append("ATTESTSTATUS = ?, ");
        sql.append("ENTRYSTATUS = ?, ");
        if (StringUtil.isNotBlank(entity.getTempLicensePlate())) {
            sql.append("TEMPLICENSEPLATE = ?, ");        	
        }
        sql.append("UPDATETIME = NOW() ");
        sql.append("WHERE SERIAL = ?");
        try {
            ps = conn.prepareStatement(sql.toString());
            int i = 1;
            ps.setString(i++,entity.getPhoto_1());
            ps.setString(i++,entity.getPhoto_2());
            ps.setString(i++,entity.getPhoto_3());
            ps.setString(i++,entity.getPhoto_4());
            ps.setString(i++,entity.getPhoto_5());
            ps.setString(i++,entity.getPhoto_6());
            ps.setString(i++,entity.getPhoto_7());
            ps.setString(i++,entity.getPhoto_8());
            ps.setInt(i++,entity.getColor());
            ps.setInt(i++,entity.getMileage());
            ps.setInt(i++,entity.getAttestStatus());
            ps.setInt(i++,entity.getEntryStatus());
            if (StringUtil.isNotBlank(entity.getTempLicensePlate())) {
                ps.setString(i++,entity.getTempLicensePlate());
            }
            ps.setString(i++,entity.getSerial());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new TmsDaoException(e.toString());
        } finally {
            close(rs, ps);
        }
    }
    
    @Override
    public void updateMarketCarInfoPhotoByID(MarketCarInfoDto entity) throws TmsDaoException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" UPDATE  ");
        sql.append(TmsTableNames.TMS_MARKET_CAR_INFO);
        sql.append(" SET PHOTO_1 = ?, ");
        sql.append("PHOTO_2 = ?, ");
        sql.append("PHOTO_3 = ?, ");
        sql.append("PHOTO_4 = ?, ");
        sql.append("PHOTO_5 = ?, ");
        sql.append("PHOTO_6 = ?, ");
        sql.append("PHOTO_7 = ?, ");
        sql.append("PHOTO_8 = ?, UPDATETIME=now() ");
        sql.append("WHERE ID = ?");
        try {
            ps = conn.prepareStatement(sql.toString());
            int i = 1;
            ps.setString(i++,entity.getPhoto_1());
            ps.setString(i++,entity.getPhoto_2());
            ps.setString(i++,entity.getPhoto_3());
            ps.setString(i++,entity.getPhoto_4());
            ps.setString(i++,entity.getPhoto_5());
            ps.setString(i++,entity.getPhoto_6());
            ps.setString(i++,entity.getPhoto_7());
            ps.setString(i++,entity.getPhoto_8());
            ps.setInt(i++,entity.getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new TmsDaoException(e.toString());
        } finally {
            close(rs, ps);
        }
    }
    
    @Override
    public int findMarketCarInfoServiceBySerial(String serial) throws TmsDaoException {
    	PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;
        StringBuffer sql = new StringBuffer("SELECT COUNT(*) COUNT FROM ");
        sql.append(TmsTableNames.TMS_MARKET_CAR_SERVICE);
        sql.append(" WHERE SERIAL = ? ");
        try {
            ps = conn.prepareStatement(sql.toString());
            ps.setString(1,serial);
            rs = ps.executeQuery();
            if (rs.next()){
            	count = rs.getInt("COUNT");
            }
        } catch (SQLException e) {
            throw new TmsDaoException(e.toString());
        } finally {
            close(rs, ps);
        }
        return count;
    }
    
    @Override
    public void updateMarketCarService(MarketCarServiceDto entity) throws TmsDaoException {
    	 PreparedStatement ps = null;
         ResultSet rs = null;
         StringBuffer sql = new StringBuffer();
         sql.append(" UPDATE  ");
         sql.append(TmsTableNames.TMS_MARKET_CAR_SERVICE);
         sql.append(" SET CHECKIMGPATH = ?, ");
         sql.append(" CHECKSTATUS = ?, ");
         sql.append(" CHECKUSER = ?, ");
         sql.append(" CHECKTIME = ?, ");
         sql.append(" CHECKVALIDTIME = ?, ");
         sql.append(" ISVALID = ? ");
         sql.append(" WHERE SERIAL = ?");
         try {
             ps = conn.prepareStatement(sql.toString());
             int i = 1;
             ps.setString(i++,entity.getCheckImgPath());
             ps.setInt(i++,entity.getCheckStatus());
             ps.setString(i++,entity.getCheckUser());
             ps.setTimestamp(i++,entity.getCheckTime());
             ps.setTimestamp(i++,entity.getCheckValidTime());
             ps.setInt(i++,entity.getIsValid());
             ps.setString(i++,entity.getSerial());
             ps.executeUpdate();
         } catch (SQLException e) {
             throw new TmsDaoException(e.toString());
         } finally {
             close(rs, ps);
         }
    }
    
    @Override
    public void insertMarketCarInfoService(MarketCarServiceDto entity) throws TmsDaoException {
    	PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO ");
		sql.append(TmsTableNames.TMS_MARKET_CAR_SERVICE);
		sql.append(" ( ");
		sql.append("	SERIAL,");
		sql.append("	CHECKIMGPATH,");
		sql.append("	CHECKSTATUS,");
		sql.append("	CHECKUSER,");
		sql.append("	CHECKTIME,");
		sql.append("	CHECKVALIDTIME,");
		sql.append("	ISVALID");
		sql.append(") VALUES(");
		sql.append("?,?,?,?,?,null,?)");
		try {
			ps = conn.prepareStatement(sql.toString());
			int i=1;
			ps.setString(i++, entity.getSerial());
			ps.setString(i++, entity.getCheckImgPath());
			ps.setInt(i++, entity.getCheckStatus());
			ps.setString(i++, entity.getCheckUser());
			ps.setTimestamp(i++, entity.getCheckTime());
			ps.setInt(i++, entity.getIsValid());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
    }
    
    
    @Override
    public int findMarketCarInfoTempCheckStatusBySerial(String serial) throws TmsDaoException {
    	PreparedStatement ps = null;
        ResultSet rs = null;
        int checkstatus = 0;
        StringBuffer sql = new StringBuffer("SELECT CHECKSTATUS FROM ");
        sql.append(TmsTableNames.TMSAPP_MARKET_CAR_INFO_TEMP);
        sql.append(" WHERE SERIAL = ? ");
        try {
            ps = conn.prepareStatement(sql.toString());
            ps.setString(1,serial);
            rs = ps.executeQuery();
            if (rs.next()){
            	checkstatus = rs.getInt("CHECKSTATUS");
            }
        } catch (SQLException e) {
            throw new TmsDaoException(e.toString());
        } finally {
            close(rs, ps);
        }
        return checkstatus;
    }
    
    
    @Override
    public void insertMarketCarInfoTemp(MarketCarInfoTempDto entity) throws TmsDaoException {
    	PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO ");
		sql.append(TmsTableNames.TMSAPP_MARKET_CAR_INFO_TEMP);
		sql.append(" ( ");
		sql.append("	SERIAL,");
		sql.append("	VIN,");
		sql.append("	PHOTO_VIN,");
		sql.append("	PHOTO_1,");
		sql.append("	PHOTO_2,");
		sql.append("	PHOTO_3,");
		sql.append("	PHOTO_4,");
		sql.append("	PHOTO_5,");
		sql.append("	PHOTO_6,");
		sql.append("	PHOTO_7,");
		sql.append("	PHOTO_8,");
		sql.append("	MILEAGE,");
		sql.append("	COLOR,");
		sql.append("	ATTESTSTATUS,");
		sql.append("	CHECKSTATUS,");
		sql.append("	CHECKIMGPATH,");
		sql.append("	CHECKPHOTO_1,");
		sql.append("	CHECKPHOTO_2,");
		sql.append("	CHECKPHOTO_3,");
		sql.append("	CHECKPHOTO_4,");
		sql.append("	CHECKPHOTO_5,");
		sql.append("	CHECKPHOTO_6,");
		sql.append("	CHECKPHOTO_7,");
		sql.append("	CHECKPHOTO_8,");
		sql.append("	CHECKPHOTO_9,");
		sql.append("	CHECKPHOTO_10,");
		sql.append("	CHECKPHOTO_11,");
		sql.append("	CHECKPHOTO_12,");
		sql.append("	CHECKPHOTO_13,");
		sql.append("	CHECKPHOTO_14,");
		sql.append("	CHECKPHOTO_15,");
		sql.append("	INSERTTIME,");
		sql.append("	INSERTUSER,");
		sql.append("	TAG1SKIP,");
		sql.append("	TAG2SKIP,");
		sql.append("	TAG3SKIP,");
		sql.append("	TEMPLICENSEPLATE,");
		sql.append("	ISMATCH");
		sql.append(")VALUES(");
		sql.append("?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		try {
			ps = conn.prepareStatement(sql.toString());
			int i=1;
			if(!StringUtil.isNullOrBlank(entity.getSerial())){
				ps.setString(i++, entity.getSerial());
			}else{
				ps.setString(i++, null);
			}
			ps.setString(i++, entity.getVin());
			ps.setString(i++, entity.getVinPhoto());
			ps.setString(i++, entity.getPhoto1());
			ps.setString(i++, entity.getPhoto2());
			ps.setString(i++, entity.getPhoto3());
			ps.setString(i++, entity.getPhoto4());
			ps.setString(i++, entity.getPhoto5());
			ps.setString(i++, entity.getPhoto6());
			ps.setString(i++, entity.getPhoto7());
			ps.setString(i++, entity.getPhoto8());
			ps.setInt(i++, entity.getMileage());
			ps.setInt(i++, entity.getColor());
			ps.setInt(i++, entity.getAttestStatus());
			ps.setInt(i++, entity.getCheckStatus());
			ps.setString(i++, entity.getCheckImgPath());
			ps.setString(i++, entity.getCheckPhoto_1());
			ps.setString(i++, entity.getCheckPhoto_2());
			ps.setString(i++, entity.getCheckPhoto_3());
			ps.setString(i++, entity.getCheckPhoto_4());
			ps.setString(i++, entity.getCheckPhoto_5());
			ps.setString(i++, entity.getCheckPhoto_6());
			ps.setString(i++, entity.getCheckPhoto_7());
			ps.setString(i++, entity.getCheckPhoto_8());
			ps.setString(i++, entity.getCheckPhoto_9());
			ps.setString(i++, entity.getCheckPhoto_10());
			ps.setString(i++, entity.getCheckPhoto_11());
			ps.setString(i++, entity.getCheckPhoto_12());
			ps.setString(i++, entity.getCheckPhoto_13());
			ps.setString(i++, entity.getCheckPhoto_14());
			ps.setString(i++, entity.getCheckPhoto_15());
			ps.setTimestamp(i++, entity.getInsertTime());
			ps.setString(i++, entity.getInsertUser());
			ps.setInt(i++, entity.getTag1Skip());
			ps.setInt(i++, entity.getTag2Skip());
			ps.setInt(i++, entity.getTag3Skip());
			ps.setString(i++, entity.getTempLicensePlate());
			if(entity.getIsMatch() != 0){
				ps.setInt(i++, entity.getIsMatch());
			}else{
				ps.setInt(i++, 1);
			}
			
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
    		
    }
    
    @Override
    public int findMarketCarInfoCountBySerial(String serial) throws TmsDaoException {
    	PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		StringBuffer sql = new StringBuffer("SELECT COUNT(*) AS COUNT FROM ");
		sql.append(TmsTableNames.TMS_MARKET_CAR_INFO);
		sql.append(" WHERE SERIAL = ?");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, serial);
			rs = ps.executeQuery();
			if (rs.next()) {
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
		return count;
    }
    
    
    
    @Override
	public void insertMarketCarInfo(MarketCarInfoDto entity) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO ");
		sql.append(TmsTableNames.TMS_MARKET_CAR_INFO);
		sql.append(" ( ");
		sql.append("	SERIAL,");
		sql.append("	COLOR,");
		sql.append("	LICENSEPLATE,");
		sql.append("	ISSHELVE,");
		sql.append("	ENTRYSTATUS,");
		sql.append("	REGISTERSTATUS,");
		sql.append("	MEMBERID,");
		sql.append("	INSERTTIME,");
		sql.append("	UPDATETIME,");
		sql.append("	INSERTUSERID,");
		sql.append("	UPDATEUSERID");
		sql.append(")VALUES(");
		sql.append("?,?,?,?,?,?,?,now(),now(),?,?)");
		try {
			ps = conn.prepareStatement(sql.toString());
			int i = 1;
			ps.setString(i++, entity.getSerial());
			ps.setInt(i++, entity.getColor());
			ps.setString(i++, entity.getLicensePlate());
			ps.setInt(i++, entity.getIsShelve());
			ps.setInt(i++, entity.getEntryStatus());
			ps.setInt(i++, entity.getRegisterStatus());
			ps.setInt(i++, entity.getMemberId());
			ps.setInt(i++, entity.getInsertUserId());
			ps.setInt(i++, entity.getUpdateUserId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
	}

	@Override
	public void updateSyncMarketCarInfo(MarketCarInfoDto entity) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ");
		sql.append(TmsTableNames.TMS_MARKET_CAR_INFO);
		sql.append(" SET ");
		sql.append("	COLOR=?,");
		sql.append("	LICENSEPLATE=?,");
		sql.append("	ISSHELVE=?,");
		sql.append("	ENTRYSTATUS=?,");
		sql.append("	REGISTERSTATUS=?,");
		sql.append("	MEMBERID=?,");

		sql.append("	CHARGESTARTDATE=NULL,");
		sql.append("	INSERTTIME=NOW(),");
		sql.append("	UPDATETIME=NOW(),");
		sql.append("	INSERTUSERID=?,");
		sql.append("	UPDATEUSERID=?");
		sql.append(" WHERE ID = ?");
		try {
			ps = conn.prepareStatement(sql.toString());
			int i = 1;
			ps.setInt(i++, entity.getColor());
			ps.setString(i++, entity.getLicensePlate());
			ps.setInt(i++, entity.getIsShelve());
			ps.setInt(i++, entity.getEntryStatus());
			ps.setInt(i++, entity.getRegisterStatus());
			ps.setInt(i++, entity.getMemberId());
			ps.setInt(i++, entity.getInsertUserId());
			ps.setInt(i++, entity.getUpdateUserId());
			ps.setInt(i++, entity.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
	}

	@Override
	public void updateMarketCarInfoByID(MarketCarInfoDto entity) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ");
		sql.append(TmsTableNames.TMS_MARKET_CAR_INFO);
		sql.append(" SET ");
		sql.append("	CATCLASS=?,");
		sql.append("	VENDORID=?,");
		sql.append("	BRANDID=?,");
		sql.append("	CARSTYLEID=?,");
		sql.append("	MILEAGE=?,");
		sql.append("	PRICE=?,");
		sql.append("	COLOR=?,");
		sql.append("	REGDATE=?,");
		sql.append("	JQX=?,");
		sql.append("	YEARCAREFULPERIOD=?,");
		sql.append("	COMPANYCONTACTPERSON=?,");
		sql.append("	COMPANYCONTACTPHONE=?,");
		sql.append("	REMARK=?,");
		sql.append("	CARNAME=?,");
		sql.append("	UPDATETIME=NOW(),");
		sql.append("	UPDATEUSERID=?");
		sql.append(" WHERE ID = ?");
		try {
			ps = conn.prepareStatement(sql.toString());
			int i = 1;
			ps.setInt(i++, entity.getCatClass());
			ps.setString(i++, entity.getVendorId());
			ps.setString(i++, entity.getBrandId());
			ps.setString(i++, entity.getCarStyleId());
			ps.setInt(i++, entity.getMileage());
			ps.setInt(i++, entity.getPrice());
			ps.setInt(i++, entity.getColor());
			ps.setString(i++, entity.getRegDate());
			ps.setString(i++, entity.getJqx());
			ps.setString(i++, entity.getYearCarefulPeriod());
			ps.setString(i++, entity.getCompanyContactPerson());
			ps.setString(i++, entity.getCompanyContactPhone());
			ps.setString(i++, entity.getRemark());
			ps.setString(i++, entity.getCarName());
			ps.setInt(i++, entity.getUpdateUserId());
			ps.setInt(i++, entity.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
	}

	@Override
	public MarketCarInfoDto findMarketCarInfoListByLicensePlate(String licensePlate) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer("SELECT SERIAL,VIN,VENDORID,BRANDID,CARSTYLEID,CARNAME,ISMATCH,PHOTO_1,MEMBERID FROM ");
		sql.append(TmsTableNames.TMS_MARKET_CAR_INFO);
		sql.append(" WHERE LICENSEPLATE = ? ");
		sql.append(" AND  REGISTERSTATUS NOT IN (3,4) ");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, licensePlate);
			rs = ps.executeQuery();
			MarketCarInfoDto obj = null;
			if (rs.next()) {
				obj = new MarketCarInfoDto();
				obj.setSerial(rs.getString("SERIAL"));
				obj.setVin(rs.getString("VIN"));
				obj.setVendorId(rs.getString("VENDORID"));
				obj.setBrandId(rs.getString("BRANDID"));
				obj.setCarStyleId(rs.getString("CARSTYLEID"));
				obj.setCarName(rs.getString("CARNAME"));
				obj.setIsMatch(rs.getInt("ISMATCH"));
				obj.setPhoto_1(rs.getString("PHOTO_1"));
				obj.setMemberId(rs.getInt("MEMBERID"));
				
			}
			return obj;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
		
	}
	
	@Override
	public MarketCarInfoDto findMarketCarInfoListByVinAll(String vin) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		MarketCarInfoDto obj = null;
		StringBuffer sql = new StringBuffer("SELECT SERIAL,VIN,VENDORID,BRANDID,CARSTYLEID,CARNAME,ISMATCH,PHOTO_1,MEMBERID FROM ");
		sql.append(TmsTableNames.TMS_MARKET_CAR_INFO);
		sql.append(" WHERE VIN = ? ");
		sql.append(" AND  LICENSEPLATE = ?");
		sql.append(" AND  REGISTERSTATUS NOT IN (3,4) ");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, vin);
			ps.setString(2, "");
			rs = ps.executeQuery();
			if (rs.next()) {
				obj = new MarketCarInfoDto();
				obj.setSerial(rs.getString("SERIAL"));
				obj.setVin(rs.getString("VIN"));
				obj.setVendorId(rs.getString("VENDORID"));
				obj.setBrandId(rs.getString("BRANDID"));
				obj.setCarStyleId(rs.getString("CARSTYLEID"));
				obj.setCarName(rs.getString("CARNAME"));
				obj.setIsMatch(rs.getInt("ISMATCH"));
				obj.setPhoto_1(rs.getString("PHOTO_1"));
				obj.setMemberId(rs.getInt("MEMBERID"));
				
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
		return obj;
	}

	@Override
	public MarketCarInfoDto findMarketCarInfoBySerial(String serial) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		MarketCarInfoDto entity = null;
		StringBuffer sql = new StringBuffer("SELECT ID FROM ");
		sql.append(TmsTableNames.TMS_MARKET_CAR_INFO);
		sql.append(" WHERE SERIAL = ? ");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1,serial);
			rs = ps.executeQuery();
			if (rs.next()){
				entity = new MarketCarInfoDto();
				entity.setId(rs.getInt("ID"));
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
		return entity;
	}

	@Override
    public MarketCarServiceDto findMarketCarServiceBySerial(String serial) throws TmsDaoException {
    	PreparedStatement ps = null;
        ResultSet rs = null;
        MarketCarServiceDto entity = null;
        StringBuffer sql = new StringBuffer("SELECT TRANSFERPRICE,CHECKPRICE, SERIAL FROM ");
        sql.append(TmsTableNames.TMS_MARKET_CAR_SERVICE);
        sql.append(" WHERE SERIAL = ? ");
        try {
            ps = conn.prepareStatement(sql.toString());
            ps.setString(1,serial);
            rs = ps.executeQuery();
            if (rs.next()){
            	entity = new MarketCarServiceDto();
            	entity.setSerial(rs.getString("SERIAL"));
            	entity.setTransferPrice(rs.getInt("TRANSFERPRICE"));
            	entity.setCheckPrice(rs.getInt("CHECKPRICE"));
            }
        } catch (SQLException e) {
            throw new TmsDaoException(e.toString());
        } finally {
            close(rs, ps);
        }
        return entity;
    }
    
    @Override
    public void updateMarketCarInfoService(MarketCarServiceDto entity) throws TmsDaoException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" UPDATE  ");
        sql.append(TmsTableNames.TMS_MARKET_CAR_SERVICE);
        sql.append(" SET CHECKSTATUS = ?, ");
        sql.append("CHECKIMGPATH = ?, ");
        sql.append("CHECKUSER = ?, ");
        sql.append("CHECKTIME = ?, ");
        sql.append("CHECKVALIDTIME = ?, ");
        sql.append("ISVALID = ?, ");
        sql.append("CHECKPRICE = ?, ");
        sql.append("TRANSFERPRICE = ? ");
        sql.append("WHERE SERIAL = ?");
        try {
            ps = conn.prepareStatement(sql.toString());
            int i = 1;
            ps.setInt(i++,entity.getCheckStatus());
            ps.setString(i++,entity.getCheckImgPath());
            ps.setString(i++,entity.getCheckUser());
            ps.setTimestamp(i++,entity.getCheckTime());
            ps.setTimestamp(i++,entity.getCheckValidTime());
            ps.setInt(i++,entity.getIsValid());
            ps.setInt(i++,entity.getCheckPrice());
            ps.setInt(i++,entity.getTransferPrice());
            ps.setString(i++,entity.getSerial());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new TmsDaoException(e.toString());
        } finally {
            close(rs, ps);
        }
    }

    @Override
    public void updateMarketCarInfoAttestStatus(MarketCarInfoDto entity) throws TmsDaoException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" UPDATE  ");
        sql.append(TmsTableNames.TMS_MARKET_CAR_INFO);
        sql.append(" SET ATTESTSTATUS = ? ");
        if (StringUtil.isNotBlank(entity.getTempLicensePlate())) {
            sql.append(" ,TEMPLICENSEPLATE = ? ");
        }
        sql.append(",UPDATETIME = NOW() ");
        sql.append("WHERE SERIAL = ?");
        try {
            ps = conn.prepareStatement(sql.toString());
            int i = 1;
            ps.setInt(i++,entity.getAttestStatus());
            if (StringUtil.isNotBlank(entity.getTempLicensePlate())) {
            	ps.setString(i++,entity.getTempLicensePlate());
            }
            ps.setString(i++,entity.getSerial());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new TmsDaoException(e.toString());
        } finally {
            close(rs, ps);
        }
    }

    @Override
    public void updateMarketCarInfoTempLicensePlate(MarketCarInfoDto entity) throws TmsDaoException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" UPDATE  ");
        sql.append(TmsTableNames.TMS_MARKET_CAR_INFO);
        sql.append(" SET TEMPLICENSEPLATE = ? ");
        sql.append(",UPDATETIME = NOW() ");
        sql.append("WHERE SERIAL = ?");
        try {
            ps = conn.prepareStatement(sql.toString());
            int i = 1;
            ps.setString(i++,entity.getTempLicensePlate());
            ps.setString(i++,entity.getSerial());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new TmsDaoException(e.toString());
        } finally {
            close(rs, ps);
        }
    }
    
	
	@Override
	public int findTroubleCarInfoByVin(String vin) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;

		StringBuffer sql = new StringBuffer("SELECT COUNT(*) AS COUNT FROM ");
		sql.append(TmsTableNames.TMS_TROUBLE_CAR_INFO);
		sql.append(" WHERE VIN = ? ");
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1,vin);
			rs = ps.executeQuery();
			if (rs.next()){
				count = rs.getInt("COUNT");
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
		return count;
	}
	
    @Override
    public void insertTroubleCarInfo(TroubleCarInfoDto entity) throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO ");
		sql.append(TmsTableNames.TMS_TROUBLE_CAR_INFO);
		sql.append(" ( ");
		sql.append("	VIN,");
		sql.append("	TROUBLETYPE,");
		sql.append("	REMARK,");
		sql.append("	INSERTTIME");

		sql.append(")VALUES(");
		sql.append("?,?,?,now())");
		try {
			ps = conn.prepareStatement(sql.toString());
			int i = 1;
			ps.setString(i++, entity.getVin());
			ps.setInt(i++, entity.getTroubleType());
			ps.setString(i++, entity.getRemark());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			close(rs, ps);
		}
    }
    
    @Override
    public void updateTroubleCarInfo(TroubleCarInfoDto entity) throws TmsDaoException{
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" UPDATE  ");
        sql.append(TmsTableNames.TMS_TROUBLE_CAR_INFO);
        sql.append(" SET TROUBLETYPE = ? ");
        sql.append(",INSERTTIME = NOW() ");
        sql.append("WHERE VIN = ?");
        try {
            ps = conn.prepareStatement(sql.toString());
            int i = 1;
            ps.setInt(i++,entity.getTroubleType());
            ps.setString(i++,entity.getVin());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new TmsDaoException(e.toString());
        } finally {
            close(rs, ps);
        }
    }
    
    @Override
    public void deleteTroubleCarInfo(TroubleCarInfoDto entity) throws TmsDaoException{
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" DELETE FROM   ");
        sql.append(TmsTableNames.TMS_TROUBLE_CAR_INFO);
        sql.append(" WHERE VIN = ?");
        try {
            ps = conn.prepareStatement(sql.toString());
            int i = 1;
            ps.setString(i++,entity.getVin());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new TmsDaoException(e.toString());
        } finally {
            close(rs, ps);
        }
    }
    
	@Override
	public void insertFragileStickerHistory(TmsFragileStickerHistoryDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			StringBuffer sql = new StringBuffer("INSERT INTO ");
			sql.append(TmsTableNames.TMS_FRAGILESTICKER_HISTORY);
			sql.append("(");
			sql.append(" fragileStickerID, ");
			sql.append(" memberId, ");
			sql.append(" putType,  ");
			sql.append(" serial,  ");
			sql.append(" isPay,  ");
			sql.append(" insertTime,  ");
			sql.append(" insertUserId  ");
			sql.append("  ) VALUES ( ");
			sql.append(" ?,?,?,?,?,SYSDATE(),?)");
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getFragileStickerId());
			ps.setString(2, dto.getMemberId());
			ps.setString(3, dto.getPutType());
			ps.setString(4, dto.getSerial());
			ps.setString(5, dto.getIsPay());
			ps.setInt(6, dto.getInsertUserId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		
	}
}
